<?php
$sort = '';
if (isset($_GET['sort_column']) && isset($_GET['order'])) {
    $sort .= ' ORDER BY ' . mysqli_real_escape_string($connection, $_GET['sort_column']) . ' '
            . mysqli_real_escape_string($connection, $_GET['order']);
}

$search = '';
if (isset($_GET['search'])) {
    $search .= ' WHERE book_title LIKE "%' . mysqli_real_escape_string($connection, $_GET['search']) . '%" ';
}

$sqlAllBooks = 'SELECT * FROM `books`
                INNER JOIN `books_authors` ON `books`.`book_id` = `books_authors`.`book_id`
                INNER JOIN `authors` ON `books_authors`.`author_id` = `authors`.`author_id`' . $search . $sort;
$queryAllBooks = mysqli_query($connection, $sqlAllBooks);

$data['books'] = array();
$author = array();
while ($row = mysqli_fetch_assoc($queryAllBooks)) {
    $data['books'][$row['book_id']]['book_title'] = $row['book_title'];
    $data['books'][$row['book_id']]['book_id'] = $row['book_id'];
    $author['id'] = $row['author_id'];
    $author['name'] = $row['author_name'];
    $data['books'][$row['book_id']]['authors'][] = $author;
}

$sqlAllComments = 'SELECT b.book_id, COUNT(c.content) as comCount
                   FROM books as b
                   LEFT JOIN comments as c ON  c.book_id = b.book_id ' . $search . ' GROUP BY b.book_id';
$queryAllComments = mysqli_query($connection, $sqlAllComments);

while ($row = mysqli_fetch_assoc($queryAllComments)) {
    $data['books'][$row['book_id']]['commentsCount'] = $row['comCount'];
}

$data['title'] = 'Книги';
render($data, 'templates/home_public.php');